#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''
@File    :   mysql_client.py
@Time    :   2024/11/19 11:32:04
@Author  :   xiangyu Chen
@Version :   1.0
@Contact :   yu112yu@sina.com
@License :   (C)Copyright 2017-2018, Liugroup-NLPR-CASIA
@Desc    :   None
'''

# 数据库类
import pymysql
from loguru import logger

# import os
# import sys
# sys.path.append(os.path.join(os.path.dirname(__file__), '..'))
# from setting import DB_INFO

# class MysqlClient(object):

#     # 初始化方法，建立连接
#     def __init__(self):
#         self.conn = pymysql.connect(host='127.0.0.1',
#                                     port=3306,
#                                     user='testuser',
#                                     password='testuser',
#                                     database='xag',
#                                     charset='utf8')
# # 初始化数据库连接

# # 执行SQL语句的增删改

#     def execute(self, sql):
#         try:
#             with self.conn.cursor() as cursor:
#                 cursor.execute(sql)
#                 self.conn.commit()
#                 logger.info("执行的SQL语句:{}".format(sql))
#         except Exception as e:
#             logger.error("执行SQL错误，报错原因{},执行SQL:{}".format(e, sql))
#             self.conn.rollback()

#     # 执行查询
#     def get_data(self, sql):
#         try:
#             with self.conn.cursor() as cursor:
#                 cursor.execute(sql)
#                 result = cursor.fetchall()
#                 logger.info("执行的SQL语句:{}".format(sql))
#                 return result
#         except Exception as e:
#             logger.error("执行SQL错误:{},执行SQL:{}".format(e, sql))


#     # 关闭对象
#     def close(self):
#         self.conn.close()
class SQLManager(object):

    # 初始化实例方法
    def __init__(self):
        self.conn = None
        self.cursor = None
        self.connect()  #调用自己实例connect方法

    # 连接数据库
    def connect(self):
        self.conn = pymysql.connect(host='127.0.0.1',
                                    port=3306,
                                    database='xag',
                                    user='testuser',
                                    password='testuser',
                                    charset='utf8')
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

    # 查询多条数据sql是sql语句，args是sql语句的参数
    def get_list(self, sql, args=None):
        self.cursor.execute(sql, args)
        result = self.cursor.fetchall()
        return result

    # 查询单条数据
    def get_one(self, sql, args=None):
        self.cursor.execute(sql, args)
        result = self.cursor.fetchone()
        return result

    # 查询单条数据，并直接获取值列表
    def get_one_values(self, sql, args=None):
        self.cursor.execute(sql, args)
        result = list(self.cursor.fetchone().values())
        return result

    # 执行单条SQL语句
    def moddify(self, sql, args=None):
        self.cursor.execute(sql, args)
        self.conn.commit()

    # 执行多条SQL语句
    def multi_modify(self, sql, args=None):
        self.cursor.executemany(sql, args)
        self.conn.commit()

    # 创建单条记录的语句
    def create(self, sql, args=None):
        self.cursor.execute(sql, args)
        self.conn.commit()
        last_id = self.cursor.lastrowid
        return last_id

    # 关闭数据库cursor和连接
    def close(self):
        self.cursor.close()
        self.conn.close()

    # 进入with语句自动执行
    def __enter__(self):
        return self

    # 退出with语句块自动执行
    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()


if __name__ == '__main__':

    # cli = MysqlClient()
    # print(cli.get_data('select * from students'))
    # cli.close()

    # conn = pymysql.connect(host='127.0.0.1',
    #                                     port=3306,
    #                                     user='testuser',
    #                                     password='testuser',
    #                                     database='xag',
    #                                     charset='utf8')

    # cursor= conn.cursor()
    # cursor.execute('select * from students')
    # print( cursor.fetchall())
    # cursor.close()
    # conn.close()
    with SQLManager() as sqlC:
        aa = sqlC.get_one('select students_name,sex from students').values()
        # 可list后用索引取
        print(list(aa)[0])
        # 可转可迭代对象后用next()取第一个
        print(next(iter(aa)))
        print(aa)
        bb = iter(aa)
        #  可转可迭代对象后用next()+while 取全部值
        while True:
            try:
                print(next(bb))
            except StopIteration:
                break

        cc = sqlC.get_one_values('select students_name,sex from students')
    print(cc[1])
